﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _6_02_DataAdapter数据抽取DataSet_内存_
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
  

        //更新数据
        private void BindData()
        {
            string connStr = "server=.;database=TestDB;uid=sa;pwd=200234.A";

            //2.创建链接对象 打开连接
            SqlConnection sqlconn = new SqlConnection(connStr);
            sqlconn.Open();
            //3.编写sql执行语句
            string sql = "select * from Member";
            //4.执行sql语句
            SqlDataAdapter adp = new SqlDataAdapter(sql, sqlconn);
            //存入内存中的表
            DataTable dt = new DataTable();
            adp.Fill(dt);
            this.dataGridView1.DataSource = dt;
            //显示人数
            adp.SelectCommand.CommandText = "select count(*) from Member";
            int cont = (int)adp.SelectCommand.ExecuteScalar();
            this.lalCount.Text = "会员人数：" + cont.ToString();
            sqlconn.Close();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            BindData();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string connStr = "server=.;database=TestDB;uid=sa;pwd=200234.A";

            //2.创建链接对象 打开连接
            SqlConnection sqlconn = new SqlConnection(connStr);
            sqlconn.Open();
            //3.编写sql执行语句
            string sql = "select * from Member";
            //4.执行sql语句
            SqlDataAdapter adp = new SqlDataAdapter(sql, sqlconn);
            //存入内存中的表
            DataTable dt = new DataTable();
            adp.Fill(dt);
            //添加
            DataRow dr = dt.NewRow();
            dr["MemberAccount"] = "zhaoyun";     // 8个字符，符合 6~12
            dr["MemberPwd"] = "123456";
            dr["MemberName"] = "赵云";
            dr["MemberPhone"] = "13912345678";
            dt.Rows.Add(dr);
            //修改密码
            dt.Rows[1]["MemberPwd"] = "1234567";
            //删除
            //dt.Rows[1].Delete();
            //回写数据库
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(adp);
            adp.Update(dt);
            sqlconn.Close();
            this.BindData();
        }
    }
}
